from settings import *
from utils.basic_function import geom_to_ewkt
from utils.database_table import FusedRoadTable
from shapely.geometry import Point
import pandas as pd
import geopandas as geopd


def number_all_new_road(road_table_prefix):
    number_first_intersect(road_table_prefix)
    number_second_intersect(road_table_prefix)
    number_third_intersect(road_table_prefix)


def number_first_intersect(road_table_prefix):
    """
    提取出与已有路网直接相交的新增路段并编号。
    编号规则：路线编码（lxbm）共10位，前2位为'N1'，后8位为从1开始的数字编号，左边用0补齐。路段编码（ldbm）为在路线编码后加上'001'

    Args:
        road_table_prefix:
    """
    lx_num = 1
    new_road_table = FusedRoadTable(f"{road_table_prefix}_n")
    for r_class in R_CLASS_LIST[:-1]:
        match_road_table = FusedRoadTable(f"{road_table_prefix}_{r_class}".lower())
        # 一级相交
        sql = f"""select a.{new_road_table.pk} from {new_road_table.name} a, {match_road_table.name} b
            where a.{new_road_table.segment_code}='N'
            and st_intersects(a.{new_road_table.geom},st_buffer(b.{match_road_table.geom},{CLUSTER_TOLERANCE_R}))
            """
        df = pd.read_sql(sql, conn)
        for idx in df.index:
            lxbm = 'N1{:0>8d}'.format(lx_num)
            ldbm = lxbm + "001"
            new_road_table.update_attr(new_road_table.pk, df.loc[idx, new_road_table.pk],
                                       {new_road_table.segment_code: ldbm, new_road_table.road_code: lxbm})
            lx_num += 1


def number_second_intersect(road_table_prefix):
    """
    二级相交。与一级相交新增路段相交。路线编码以'N2'开头。
    """
    lx_num = 1
    new_road_table = FusedRoadTable(f"{road_table_prefix}_n")
    geom_col = new_road_table.geom
    sql = f"""select a.{new_road_table.pk} from
                (select {new_road_table.pk}, {geom_col} from {new_road_table.name} where {new_road_table.segment_code}='N') a,
                (select st_buffer({geom_col}, {CLUSTER_TOLERANCE_R}) as geom_
                    from {new_road_table.name} where substring({new_road_table.segment_code},1,2)='N1') b
            where st_intersects(a.{geom_col},b.geom_)
            """
    df = pd.read_sql(sql, conn)

    for idx in df.index:
        lxbm = 'N2{:0>8d}'.format(lx_num)
        ldbm = lxbm + "001"
        new_road_table.update_attr(new_road_table.pk, df.loc[idx, new_road_table.pk],
                                   {new_road_table.segment_code: ldbm, new_road_table.road_code: lxbm})
        lx_num += 1


def number_third_intersect(road_table_prefix):
    """
    三级相交。除一级相交、二级相交以外都归为三级相交。路线编码以'N3'开头。
    """
    lx_num = 1
    new_road_table = FusedRoadTable(f"{road_table_prefix}_n")
    sql = f"""select {new_road_table.pk} from {new_road_table.name} where {new_road_table.segment_code}='N'
            """
    df = pd.read_sql(sql, conn)
    for idx in df.index:
        lxbm = 'N3{:0>8d}'.format(lx_num)
        ldbm = lxbm + "001"
        new_road_table.update_attr(new_road_table.pk, df.loc[idx, new_road_table.pk],
                                   {new_road_table.segment_code: ldbm, new_road_table.road_code: lxbm})
        lx_num += 1
